
import sqlite3
import sqlitehelper

class Book:
    id = ''
    name = ''
    auth = ''
    imgurl = ''
    last_update = ''
    last_update_content = ''
    last_update_content_url = ''
    last_update_content_id = ''

    def save_to_db(self):
        cx = sqlite3.connect(sqlitehelper.SQLITE_FILE)
        cu = cx.cursor()
        sql = '''
        REPLACE INTO Book
  (
    id ,
    name ,
    auth ,
    imgurl,
    last_update ,
    last_update_content ,
    last_update_content_url ,
    last_update_content_id
  )
VALUES
  (
    ?,?,?,?,?,?,?,?
  ) ;
        '''
        res = cu.execute(sql,(self.id ,
    self.name ,
    self.auth ,
    self.imgurl,
    self.last_update ,
    self.last_update_content ,
    self.last_update_content_url ,
    self.last_update_content_id))

        cx.commit()

        return res

    @classmethod
    def created_table(self):
        cx = sqlite3.connect(sqlitehelper.SQLITE_FILE)
        cu = cx.cursor()
        res = cu.execute(self.sql_created_table())
        cx.commit()

    @classmethod
    def find_all(self,where=None):
        sql = '''select id ,
    name ,
    auth ,
    imgurl,
    last_update ,
    last_update_content ,
    last_update_content_url ,
    last_update_content_id FROM Book where id NOT NULL '''

        if where != None:
            sql += " and {}".format(where)

        cx = sqlite3.connect(sqlitehelper.SQLITE_FILE)
        cu = cx.cursor()
        rows = cu.execute(sql)
        res = list()
        for row in rows:
            obj = Book()
            obj.id = row[0]
            obj.name = row[1]
            obj.auth = row[2]
            obj.imgurl = row[3]
            obj.last_update = row[4]
            obj.last_update_content = row[5]
            obj.last_update_content_url = row[6]
            obj.last_update_content_id = row[7]
            res.append(obj)
        cx.close()
        return res


    @classmethod
    def sql_created_table(self):
        return '''CREATE TABLE IF NOT EXISTS {} (id integer primary key,
    name TEXT,
    auth TEXT,
    imgurl TEXT,
    last_update TEXT,
    last_update_content TEXT,
    last_update_content_url TEXT,
    last_update_content_id integer)'''.format(self.__name__)




class BookUnit:
    id = ''
    bookid = ''
    url = ''
    title = ''
    absurl = ''
    content = ''
    def __init__(self,id,bookid,url,title,absurl = '',content = ''):
        super().__init__()
        self.id = id
        self.bookid = bookid
        self.url = url
        self.title = title
        self.absurl = absurl
        self.content = content

    def sql_insert(self):
        return '''
            INSERT INTO {4} (id, bookid, title,url) VALUES ('{0}', '{1}', '{2}','{3}');
        '''.format(self.id,self.bookid,self.title,self.url,type(self).__name__)

    @classmethod
    def sql_created_table(self):
        return '''CREATE TABLE IF NOT EXISTS {} (id integer primary key,bookid integer,title TEXT,url TEXT,absUrl TEXT,content TEXT)'''.format(self.__name__)

    @classmethod
    def find_all(self,where):
        cx = sqlite3.connect(sqlitehelper.SQLITE_FILE)
        cu =  cx.cursor()
        sql_select="SELECT id,bookid,url,title,absurl,content FROM BookUnit"
        if where != None:
            sql_select += " WHERE " + where
        cu.execute(sql_select)
        res = list()
        for row in cu:
            obj = BookUnit(row[0],row[1],row[2],row[3],row[4],row[5])
            res.append(obj)
        cx.close()
        return res

    @classmethod
    def find_all_no_content(self,bookid=None):
        cx = sqlite3.connect(sqlitehelper.SQLITE_FILE)
        cu =  cx.cursor()
        sql_select="SELECT id,bookid,url,title FROM BookUnit where content is null"
        if bookid != None:
            sql_select += " and bookid = '{}' ".format(bookid)

        cu.execute(sql_select)
        res = list()
        for row in cu:
            res.append(BookUnit(row[0],row[1],row[2],row[3]))
        cx.close()
        return res

    def update_to_db(self,absurl,content):
        self.absurl  = absurl
        self.content = content
        cx = sqlite3.connect(sqlitehelper.SQLITE_FILE)
        cu =  cx.cursor()
        res = cu.execute("UPDATE {TABLENAME} SET absurl=?,content=? WHERE id = ?".format(TABLENAME = type(self).__name__),(absurl,content,self.id))
        cx.commit()
        cx.close()
        return res

class UnitContent:
    id = ''
    content = ''
    url = ''

    def __init__(self,id,content,url):
        super().__init__()
        self.id = id
        self.content = content
        self.url = url;

    def sql_insert(self):
        return '''
            INSERT INTO {TABLENAME} (id, content,url) VALUES ('{id}', ?,'{url}');
        '''.format(id=self.id,TABLENAME=type(self).__name__,url=self.url)


    @classmethod
    def sql_created_table(self):
        return '''CREATE TABLE IF NOT EXISTS {} (id integer primary key,content TEXT,url TEXT)'''.format(self.__name__)

